package com.newtouch.bxzs.database.synchronize.mapper;

import com.newtouch.bxzs.database.synchronize.model.CustomerBO;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;

/**
 * 客户Mapper
 *
 * <p>
 * <b>History:</b>
 * <table border="0">
 * <tr>
 * <th>Date</th>
 * <th>Operator</th>
 * <th>mome</th>
 * </tr>
 * <tr>
 * <td>2020/02/30 00:00</td>
 * <td>Amour</td>
 * <td>Create</td>
 * </tr>
 * </table>
 *
 * @author yuanjie
 * @version 0.0.0
 * @since 0.0.0
 */
@Mapper
public interface CustomerMapper{

    /**
     * 批量添加客户数据
     * @param customer 客户数据
     * @param createTime 创建时间
     * @return int
     */
    @Insert({"<script>" +
            "insert into ASSISTANT_CUSTOMER(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_SEX,CUSTOMER_PHONE," +
            "CUSTOMER_TELEPHONE,CUSTOMER_CERTIFICATE_TYPE,CUSTOMER_CERTIFICATE_NUMBER,CUSTOMER_NAME_FULL_SPELLING," +
            "CUSTOMER_BIRTHDAY,CUSTOMER_OCCUPATION,CUSTOMER_FAMILY_ADDRESS,IS_SOCIAL_INSURANCE,CUSTOMER_COMPANY_NAME," +
            "CUSTOMER_COMPANY_ADDRESS,CUSTOMER_POSITION,CUSTOMER_ANNUAL_INCOME,CUSTOMER_MARRY_STATUS," +
            "CUSTOMER_CHILD_STATUS,CUSTOMER_HOBBY,CUSTOMER_LABEL,USER_JOB_NUMBER,IS_DELETE,CREATE_USER_ID,CREATE_TIME) " +
            "values"+
            "<foreach collection='list' item='item' separator=','>"+
            "(" +
            " REPLACE(uuid(),'-',''),#{item.customerName},#{item.customerSex},"+
            "#{item.customerPhone},#{item.customerTelephone},#{item.customerCertificateType},"+
            "#{item.customerCertificateNumber},#{item.customerNameFullSpelling}," +
            "#{item.customerBirthDay},#{item.customerOccupation},"+
            "#{item.customerFamilyAddress},#{item.socialInsurance},#{item.customerCompanyName},"+
            "#{item.customerCompanyAddress},#{item.customerPosition},"+
            "#{item.customerAnnualIncome},#{item.customerMarryStatus},"+
            "#{item.customerChildStatus},#{item.customerHobby},#{item.customerLabel},"+
            "#{item.userJobNumber},0,'admin',#{createTime}" +
            ")"+
            "</foreach>"+
            "</script>"})
    int addCustomerList(@Param("list") List<CustomerBO> customer,@Param("createTime") Date createTime);

    /**
     * 创建Customer临时表
     */
    @Update({"CREATE TABLE CUSTOMERBACKUP (" +
            "  CUSTOMER_ID varchar(32) ," +
            "  CUSTOMER_NAME varchar(80)," +
            "  CUSTOMER_SEX varchar(20) ," +
            "  CUSTOMER_PHONE varchar(11)," +
            "  CUSTOMER_TELEPHONE varchar(30)," +
            "  CUSTOMER_CERTIFICATE_TYPE varchar(20)," +
            "  CUSTOMER_CERTIFICATE_NUMBER varchar(50)," +
            "  CUSTOMER_NAME_FULL_SPELLING varchar(100)," +
            "  CUSTOMER_BIRTHDAY date ," +
            "  CUSTOMER_OCCUPATION varchar(20)," +
            "  CUSTOMER_FAMILY_ADDRESS varchar(300)," +
            "  IS_SOCIAL_INSURANCE tinyint(1)," +
            "  CUSTOMER_COMPANY_NAME varchar(100)," +
            "  CUSTOMER_COMPANY_ADDRESS varchar(300)," +
            "  CUSTOMER_POSITION varchar(20) ," +
            "  CUSTOMER_ANNUAL_INCOME decimal(10, 2)," +
            "  CUSTOMER_MARRY_STATUS tinyint(1)," +
            "  CUSTOMER_CHILD_STATUS tinyint(1)," +
            "  CUSTOMER_HOBBY varchar(200)," +
            "  CUSTOMER_LABEL varchar(10)," +
            "  USER_JOB_NUMBER varchar(20)," +
            "  IS_DELETE tinyint(1) ," +
            "  CREATE_TIME datetime(0) ," +
            "  CREATE_USER_ID varchar(32)," +
            "  UPDATE_TIME datetime(0)," +
            "  UPDATE_USER_ID varchar(32)," +
            "  MEMO varchar(300)," +
            "  PRIMARY KEY (CUSTOMER_ID) USING BTREE"+
            ")"})
    void createTable();

    /**
     * 删除Customer临时表
     */
    @Update({"drop table if exists CUSTOMERBACKUP"})
    void dropTable();

    /**
     * 临时表插入同步数据
     * @param customer 客户信息
     * @param createTime 创建时间
     * @return int
     */
    @Insert({"<script>" +
            "insert into CUSTOMERBACKUP(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMER_SEX,CUSTOMER_PHONE," +
            "CUSTOMER_TELEPHONE,CUSTOMER_CERTIFICATE_TYPE,CUSTOMER_CERTIFICATE_NUMBER,CUSTOMER_NAME_FULL_SPELLING," +
            "CUSTOMER_BIRTHDAY,CUSTOMER_OCCUPATION,CUSTOMER_FAMILY_ADDRESS,IS_SOCIAL_INSURANCE,CUSTOMER_COMPANY_NAME," +
            "CUSTOMER_COMPANY_ADDRESS,CUSTOMER_POSITION,CUSTOMER_ANNUAL_INCOME,CUSTOMER_MARRY_STATUS," +
            "CUSTOMER_CHILD_STATUS,CUSTOMER_HOBBY,CUSTOMER_LABEL,USER_JOB_NUMBER,IS_DELETE,CREATE_USER_ID,CREATE_TIME) " +
            "values"+
            "<foreach collection='list' item='item' separator=','>"+
            "(" +
            " REPLACE(uuid(),'-',''),#{item.customerName},#{item.customerSex},"+
            "#{item.customerPhone},#{item.customerTelephone},#{item.customerCertificateType},"+
            "#{item.customerCertificateNumber},#{item.customerNameFullSpelling}," +
            "#{item.customerBirthDay},#{item.customerOccupation},"+
            "#{item.customerFamilyAddress},#{item.socialInsurance},#{item.customerCompanyName},"+
            "#{item.customerCompanyAddress},#{item.customerPosition},"+
            "#{item.customerAnnualIncome},#{item.customerMarryStatus},"+
            "#{item.customerChildStatus},#{item.customerHobby},#{item.customerLabel},"+
            "#{item.userJobNumber},0,'admin',#{createTime}" +
            ")"+
            "</foreach>"+
            "</script>"})
    int addCustomer(@Param("list") List<CustomerBO> customer,@Param("createTime") Date createTime);

    /**
     * 查询需要更新客户标识的数据
     * @return  List<CustomerBO>
     */
    @Select({"SELECT" +
            " tc.CUSTOMER_ID," +
            "CASE" +
            " WHEN tc.CUSTOMER_LABEL NOT LIKE '%3%' THEN" +
            " concat( tc.CUSTOMER_LABEL, ',3' ) ELSE tc.CUSTOMER_LABEL " +
            "  END AS CUSTOMER_LABEL" +
            " FROM" +
            " CUSTOMERBACKUP cp" +
            " LEFT JOIN ASSISTANT_CUSTOMER tc ON cp.CUSTOMER_NAME = tc.CUSTOMER_NAME " +
            " AND cp.CUSTOMER_CERTIFICATE_TYPE = tc.CUSTOMER_CERTIFICATE_TYPE " +
            " AND cp.CUSTOMER_CERTIFICATE_NUMBER = tc.CUSTOMER_CERTIFICATE_NUMBER " +
            "  AND cp.USER_JOB_NUMBER = tc.USER_JOB_NUMBER  " +
            " WHERE" +
            " tc.CUSTOMER_LABEL LIKE '%0%' " +
            " OR tc.CUSTOMER_LABEL LIKE '%2%'"})
    List<CustomerBO> getcustomerLabel();

    /**
     * 从临时表获取客户表没有的客户数据
     * @return List<CustomerBO>
     */
    @Select({"select cp.* from" +
            " CUSTOMERBACKUP cp left join ASSISTANT_CUSTOMER tc" +
            " on cp.CUSTOMER_NAME = tc.CUSTOMER_NAME" +
            " and cp.CUSTOMER_CERTIFICATE_TYPE = tc.CUSTOMER_CERTIFICATE_TYPE" +
            " and cp.CUSTOMER_CERTIFICATE_NUMBER = tc.CUSTOMER_CERTIFICATE_NUMBER" +
            " and cp.USER_JOB_NUMBER = tc.USER_JOB_NUMBER" +
            " where tc.CUSTOMER_ID  is null"})
    List<CustomerBO> getAddCustomer();

    /**
     * 获取可更新的客户信息
     * @return List<CustomerBO>
     */
    @Select({"SELECT" +
            " tc.CUSTOMER_ID,cp.CUSTOMER_SEX,cp.CUSTOMER_PHONE," +
            "cp.CUSTOMER_TELEPHONE,cp.CUSTOMER_BIRTHDAY,cp.CUSTOMER_OCCUPATION," +
            "cp.CUSTOMER_FAMILY_ADDRESS,cp.IS_SOCIAL_INSURANCE,cp.CUSTOMER_COMPANY_NAME," +
            "cp.CUSTOMER_COMPANY_ADDRESS,cp.CUSTOMER_POSITION," +
            "cp.CUSTOMER_ANNUAL_INCOME,cp.CUSTOMER_MARRY_STATUS," +
            "cp.CUSTOMER_CHILD_STATUS,cp.CUSTOMER_HOBBY,cp.CUSTOMER_LABEL," +
            "cp.USER_JOB_NUMBER,cp.CREATE_USER_ID,cp.CREATE_TIME,cp.UPDATE_USER_ID,cp.UPDATE_TIME,cp.IS_DELETE, " +
            "CASE " +
            "  WHEN tc.CUSTOMER_LABEL NOT LIKE '%3%' THEN " +
            "  concat( tc.CUSTOMER_LABEL, ',3' ) ELSE tc.CUSTOMER_LABEL  " +
            "  END AS CUSTOMER_LABEL" +
            " FROM " +
            "  CUSTOMERBACKUP cp " +
            "  LEFT JOIN ASSISTANT_CUSTOMER tc ON cp.CUSTOMER_NAME = tc.CUSTOMER_NAME  " +
            "  AND cp.CUSTOMER_CERTIFICATE_TYPE = tc.CUSTOMER_CERTIFICATE_TYPE  " +
            "  AND cp.CUSTOMER_CERTIFICATE_NUMBER = tc.CUSTOMER_CERTIFICATE_NUMBER  " +
            "  AND cp.USER_JOB_NUMBER = tc.USER_JOB_NUMBER  " +
            " WHERE " +
            "  tc.CUSTOMER_LABEL not LIKE '%0%'  " +
            "  and tc.CUSTOMER_LABEL not LIKE '%2%'"})
    List<CustomerBO>getUpdateCustomer();

    /**
     * 批量修改客户标识
     * @param customers 客户信息
     * @param createTime 创建时间
     * @return int
     */
    @Update({"<script>" +
            "<foreach collection='list' item='item' separator=';'>"+
            " update ASSISTANT_CUSTOMER set " +
            "CUSTOMER_LABEL = #{item.customerLabel},"+
            "UPDATE_USER_ID = 'admin',"+
            "UPDATE_TIME = #{createTime}" +
            " where CUSTOMER_ID = #{item.customerId}" +
            "</foreach>"+
            "</script>"})
    int updatecustLabel(@Param("list")List<CustomerBO> customers,@Param("createTime") Date createTime);

    /**
     * 批量修改客户信息
     * @param customers
     * @param createTime
     * @return
     */
    @Update({"<script>" +
            "<foreach collection='list' item='item' separator=';' >" +
            " update ASSISTANT_CUSTOMER set" +
            " CUSTOMER_SEX = #{item.customerSex},"+
            "CUSTOMER_PHONE = #{item.customerPhone}," +
            "CUSTOMER_TELEPHONE = #{item.customerTelephone},"+
            "CUSTOMER_BIRTHDAY = #{item.customerBirthDay}," +
            "CUSTOMER_OCCUPATION = #{item.customerOccupation},"+
            "CUSTOMER_FAMILY_ADDRESS = #{item.customerFamilyAddress}," +
            "IS_SOCIAL_INSURANCE = #{item.socialInsurance}," +
            "CUSTOMER_COMPANY_NAME = #{item.customerCompanyName},"+
            "CUSTOMER_COMPANY_ADDRESS = #{item.customerCompanyAddress}," +
            "CUSTOMER_POSITION = #{item.customerPosition},"+
            "CUSTOMER_ANNUAL_INCOME = #{item.customerAnnualIncome}," +
            "CUSTOMER_MARRY_STATUS = #{item.customerMarryStatus},"+
            "CUSTOMER_CHILD_STATUS = #{item.customerChildStatus}," +
            "CUSTOMER_HOBBY = #{item.customerHobby}," +
            "CUSTOMER_LABEL = #{item.customerLabel},"+
            "USER_JOB_NUMBER = #{item.userJobNumber}," +
            "UPDATE_USER_ID = 'admin',"+
            "UPDATE_TIME = #{createTime}," +
            "IS_DELETE = 0"+
            " where CUSTOMER_ID = #{item.customerId}" +
            "</foreach>"+
            "</script>"})
    int updateCustomerBatch(@Param("list")List<CustomerBO> customers,@Param("createTime") Date createTime);
}
